package com.example.util.JdbcUtil;

import com.example.DbConfig.JdbcUtil;

import java.sql.*;

public class KingbaseJdbcDemo {

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // 获取连接
            System.out.println("连接数据库...");
            conn = JdbcUtil.getConnection();
            System.out.println("连接成功!");

            // 创建语句
            stmt = conn.createStatement();

            // 测试连接
            testConnection(stmt);

            // 创建表
            createTable(stmt);

            // 插入数据
            insertData(stmt);

            // 查询数据
            rs = queryData(stmt);
            printResults(rs);

            // 更新数据
            updateData(stmt);

            // 再次查询
            rs = queryData(stmt);
            printResults(rs);

            // 删除数据
            deleteData(stmt);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, stmt, rs);
        }
    }

    // 测试连接

    private static void testConnection(Statement stmt) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT 1 AS TEST");
        if (rs.next()) {
            System.out.println("连接测试成功: " + rs.getInt("TEST"));
        }
        rs.close();
    }

    // 创建表
    private static void createTable(Statement stmt) throws SQLException {
        System.out.println("创建表...");
        // 先删除表如果存在
        try {
            stmt.executeUpdate("DROP TABLE IF EXISTS TEST_USER");
        } catch (SQLException e) {
            // 忽略不存在的表
        }

        // 创建新表
        String sql = "CREATE TABLE TEST_USER (" +
                "ID INTEGER PRIMARY KEY, " +
                "NAME VARCHAR(100), " +
                "AGE INTEGER, " +
                "CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                ")";
        stmt.executeUpdate(sql);
        System.out.println("表创建成功!");
    }

    // 插入数据
    private static void insertData(Statement stmt) throws SQLException {
        System.out.println("插入数据...");
        String sql1 = "INSERT INTO TEST_USER(ID, NAME, AGE) VALUES(1, '张三', 25)";
        String sql2 = "INSERT INTO TEST_USER(ID, NAME, AGE) VALUES(2, '李四', 30)";
        stmt.executeUpdate(sql1);
        stmt.executeUpdate(sql2);
        System.out.println("数据插入成功!");
    }

    // 查询数据
    private static ResultSet queryData(Statement stmt) throws SQLException {
        System.out.println("查询数据...");
        return stmt.executeQuery("SELECT * FROM TEST_USER ORDER BY ID");
    }

    // 打印结果
    private static void printResults(ResultSet rs) throws SQLException {
        System.out.println("ID\tNAME\tAGE\tCREATE_TIME");
        System.out.println("--------------------------------------------------");
        while (rs.next()) {
            int id = rs.getInt("ID");
            String name = rs.getString("NAME");
            int age = rs.getInt("AGE");
            Timestamp createTime = rs.getTimestamp("CREATE_TIME");

            System.out.println(id + "\t" + name + "\t" + age + "\t" + createTime);
        }
        System.out.println();
    }

    // 更新数据
    private static void updateData(Statement stmt) throws SQLException {
        System.out.println("更新数据...");
        String sql = "UPDATE TEST_USER SET AGE = 26 WHERE ID = 1";
        int rows = stmt.executeUpdate(sql);
        System.out.println("更新成功, 影响行数: " + rows);
    }

    // 删除数据
    private static void deleteData(Statement stmt) throws SQLException {
        System.out.println("删除数据...");
        String sql = "DELETE FROM TEST_USER WHERE ID = 2";
        int rows = stmt.executeUpdate(sql);
        System.out.println("删除成功, 影响行数: " + rows);
    }
}